package cn.easybuy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.xml.crypto.Data;

import cn.easybuy.dao.BaseDao;
import cn.easybuy.dao.ProductDao;
import cn.easybuy.entity.Product;
import cn.easybuy.utils.DataSourceUtil;

public class ProductDaoImpl extends BaseDao implements ProductDao {

	ResultSet rs = null;
	public ProductDaoImpl(Connection conn) {
		super(conn);
		// TODO Auto-generated constructor stub
	}

	@Override
	public List<Product> findProduct() {
		String sql = "SELECT * FROM easybuy_product where isDelete='0'";
		List<Product>list = new ArrayList<Product>();
		rs = this.executeQuery(sql, null);
		try {
			while(rs.next()){
				list.add(this.rsToProduct(rs));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DataSourceUtil.closeAll(null, null, rs);
		}
		return list;
	}

	//查询RS转换Product对象
	private Product rsToProduct(ResultSet rs){
		Product product = new Product();
		try {
			product.setId(rs.getInt("id"));
			product.setName(rs.getString("name"));
			product.setDescription(rs.getString("description"));
			product.setPrice(rs.getFloat("price"));
			product.setStock(rs.getInt("stock"));
			product.setCategoryLevel1Id(rs.getInt("categoryLevel1"));
			product.setCategoryLevel2Id(rs.getInt("categoryLevel2"));
			product.setCategoryLevel3Id(rs.getInt("categoryLevel3"));
			product.setFileName(rs.getString("fileName"));
			product.setIsDelete(rs.getInt("isDelete"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return product;
	}

	@Override
	public List<Product> findProductByLevel1(String categoryLevel1) {
		String sql="select * from easybuy_product where categoryLevel1Id=? and isDelete='0'";
		//调用方法获得结果集
		Object[]objArr={categoryLevel1};
		rs=super.executeQuery(sql,objArr);
		//保存对象集合
		List<Product> productList=new ArrayList<Product>();
		try {
			while(rs.next()){
				productList.add(this.rsToProduct(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DataSourceUtil.closeAll(null, null, rs);
		}
		return productList;
	}

	@Override
	public Product findProductById(String id) {
		String sql = "select * from easybuy_product where id=?";
		Object[] objArr = {id};
		Product product = null;
		rs = this.executeQuery(sql, objArr);
		try {
			while(rs.next()){
				product= this.rsToProduct(rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DataSourceUtil.closeAll(null, null, rs);
		}
		return product;
	}

	@Override
	public int findStockById(String id) {
		int count=0;
		String sql = "select stock from easybuy_product where id=?";
		Object[] objArr = {id};
		try {
			rs = super.executeQuery(sql, objArr);
			if (rs.next()) {
				count=rs.getInt("stock");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DataSourceUtil.closeAll(null, null, rs);
		}
		return count;
	}

	@Override
	public int subtractStock(String id,int num) {
		int index=0;
		Connection conn = DataSourceUtil.getConnection();
		String sql="UPDATE easybuy_product SET stock=stock-?  WHERE id=?";
		Object[]objArr = {num,id};
		PreparedStatement ps=null;
		try {
			ps=conn.prepareStatement(sql);
			for(int i=0;i<objArr.length;i++){
				ps.setObject(i+1,objArr[i]);
			}
			num=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DataSourceUtil.closeAll(conn, ps, null);
		}
		return num;
	}

	@Override
	public List<Product> searchProduct(String name) {
		String sql="select * from easybuy_product where name like ? and isDelete='0'";
		Object[]objArr = {"%"+name+"%"};
		//调用方法获得结果集
		rs=super.executeQuery(sql,objArr);
		//将结果集转换为对象
		List<Product> productList=new ArrayList<Product>();
		try {
			while(rs.next()){
				productList.add(this.rsToProduct(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DataSourceUtil.closeAll(null, null, rs);
		}
		return productList;
	}

	@Override
	public int addProduct(Product p) {
		String sql="INSERT INTO easybuy_product(NAME,description,price,stock,categoryLevel1,categoryLevel2,categoryLevel3,fileName,isDelete) VALUES(?,?,?,?,?,?,?,?,0)";
		Object[]objArr={p.getName(),p.getDescription(),p.getPrice(),p.getStock(),p.getCategoryLevel1Id(),p.getCategoryLevel2Id(),p.getCategoryLevel3Id(),p.getFileName()};
		return super.executeUpdate(sql, objArr);
	}

	@Override
	public int delProduct(String ProductId) {
		String sql="update easybuy_product set isDelete='1' where id=?";
		Object[]objArr={ProductId};
		return super.executeUpdate(sql, objArr);
	}

	@Override
	public int updateProduct(Product p) {
		String sql="UPDATE easybuy_product SET NAME=?,description=?,price=?,stock=?,categoryLevel1=?,categoryLevel2=?,categoryLevel3=?,fileName=? WHERE id=?";
		Object[]objArr = {p.getName(),p.getDescription(),p.getPrice(),p.getStock(),p.getCategoryLevel1Id(),p.getCategoryLevel2Id(),p.getCategoryLevel3Id(),p.getFileName(),p.getId()};
		return super.executeUpdate(sql,objArr);
	}

	@Override
	public List<Product> getPageList(int pageNo,int pageSize) {
		String sql = "select * from easybuy_product where isDelete='0' limit ?,? ";
		Object[]objArr={(pageNo-1)*pageSize,pageSize};
		rs = this.executeQuery(sql, objArr);
		List<Product> list=new ArrayList<Product>();
		try {
			while(rs.next()){
				list.add(this.rsToProduct(rs));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DataSourceUtil.closeAll(null, null, rs);
		}
		return list;
	}

	@Override
	public int getCount() {
		// TODO Auto-generated method stub
		int i = 0;
		String sql = "select count(*) from easybuy_product where isDelete = 0";
		rs = this.executeQuery(sql, null);
		try {
			while(rs.next()){
				i = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DataSourceUtil.closeAll(null, null, rs);
		}
		return i;
	}

	@Override
	public int findProductToCategoryId(String categoryId) {
		String sql = "SELECT COUNT(*)FROM easybuy_product WHERE categoryLevel3=?";
		Object[]objArr = {categoryId};
		rs = this.executeQuery(sql, objArr);
		int i = 0;
		try {
			while(rs.next()){
				i = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DataSourceUtil.closeAll(null, null, rs);
		}
		return i;
	}
}
